﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    class ChargeController
    {
        private DBConnector connector;

        public ChargeController()
        {
            connector = new DBConnector();
        }

        public DataTable getData(string lastName, string firstName, int typeOfCharge)
        {
            string query;
            if(typeOfCharge==0) //0 = tip 1=salary
                query = "SELECT dateTipcharges as 'Date', FORMAT(chargedAmount,2) as 'Charged Amount', description as 'Description' FROM tipcharges WHERE employeeCode = (SELECT employeeCode FROM employee WHERE lastName = \'" + lastName + "\' and firstName = \'" + firstName + "\') ORDER BY dateTipcharges DESC;";
            else
                query = "SELECT dateSalarycharges as 'Date', FORMAT(chargedAmount,2) as 'Charged Amount', description as 'Description' FROM salarycharges WHERE employeeCode =  (SELECT employeeCode FROM employee WHERE lastName = \'" + lastName + "\' and firstName = \'" + firstName + "\') ORDER BY dateSalarycharges DESC;";
         
            if (connector.openConnection())
            {
                MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connector.getConnection());
                MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dataAdapter);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);
                connector.closeConnection();
                return dataTable;
            }
            else
            {
                return null;
            }
        }

        public bool addCharge(Charge charge, int typeOfCharge) //0 = tip, 1 = salary
        {
            string query;
            MiscChargeController miscCharge = new MiscChargeController();
            DateTime date = DateTime.Parse(charge.Date);
            int periodID = miscCharge.getPeriodID(date);
            if (periodID == -1)
            {
                MessageBox.Show("Date selected is not found in a period.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            else
            {
                if (typeOfCharge == 0)
                    query = "INSERT INTO tipcharges(employeeCode, periodID, dateTipcharges, chargedAmount, description) VALUES ((SELECT employeeCode FROM employee WHERE lastName = \'" + charge.lastName + "\' and firstName = \'" + charge.firstName + "\')," + periodID + " , \'" + charge.Date + "\'," + charge.Amount + ",\'" + charge.Description + "\');";
                else
                    query = "INSERT INTO salarycharges(employeeCode, periodID, dateSalarycharges, chargedAmount, description) VALUES ((SELECT employeeCode FROM employee WHERE lastName = \'" + charge.lastName + "\' and firstName = \'" + charge.firstName + "\'), " + periodID + ", \'" + charge.Date + "\'," + charge.Amount + ",\'" + charge.Description + "\');";

                if (connector.openConnection())
                {
                    try
                    {
                        MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                        cmd.ExecuteNonQuery();
                        connector.closeConnection();
                        return true;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("That date already exists. Input another date, or use edit to change the tip for that date.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return false;
                    }
                }

                else
                    return false;
            }
        }
        public bool editCharge(Charge charge, int typeOfCharge, string date, double amount, string description)
        {
            string query;
            if(typeOfCharge==0)
                query = "UPDATE tipcharges set dateTipcharges = \'" + charge.Date + "\', chargedAmount = " + charge.Amount + ", description = \'" + charge.Description + "\' WHERE dateTipcharges = \'" + date + "\' and chargedAmount = " + amount + " and description = \'" + description + "\' and employeeCode = (SELECT employeeCode FROM employee WHERE lastName = \'" + charge.lastName + "\' and firstName = \'" + charge.firstName + "\');";
            else
                query = "UPDATE salarycharges set dateSalarycharges = \'" + charge.Date + "\', chargedAmount = " + charge.Amount + ", description = \'" + charge.Description + "\' WHERE dateSalarycharges = \'" + date + "\' and chargedAmount = " + amount + " and description = \'" + description + "\' and employeeCode = (SELECT employeeCode FROM employee WHERE lastName = \'" + charge.lastName + "\' and firstName = \'" + charge.firstName + "\');";
         
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.CommandText = query;
                    cmd.Connection = connector.getConnection();
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("A charge with the same details already exist", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            else
                return false;
        }
        public bool deleteCharge(int typeOfCharge, string date, double amount, string description)
        {
            string query;
            if(typeOfCharge==0)
                query = "DELETE FROM tipcharges WHERE dateTipcharges = \'" + date + "\' and chargedAmount = " + amount + " and description = \'" + description + "\';";
            else
                query = "DELETE FROM salarycharges WHERE dateSalarycharges = \'" + date + "\' and chargedAmount = " + amount + " and description = \'" + description + "\';";
            
            if (connector.openConnection())
            {
                try
                {
                    MySqlCommand cmd = new MySqlCommand(query, connector.getConnection());
                    cmd.ExecuteNonQuery();
                    connector.closeConnection();
                    return true;
                }
                catch (MySqlException ex)
                {
                    MessageBox.Show("Selected charge cannot be deleted.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return false;
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                    return false;
                }
            }
            return false;
        }
    }
}
